****** Official data on consumption diesel prices ****


clear
import delimited "$path\Raw Data\Prix_agrégé_en ligne.csv", delim(";") varnames(1)

replace date=subinstr(date, "janv", "jan",.)
replace date=subinstr(date, "févr", "feb",.)
replace date=subinstr(date, "mars", "mar",.)
replace date=subinstr(date, "juin", "jun",.)
replace date=subinstr(date, "juil", "jul",.)
replace date=subinstr(date, "août", "aug",.)
replace date=subinstr(date, "sept", "sep",.)
replace date=subinstr(date, "déc", "dec",.)



replace date="01-jan-10" if date=="31-dec-09" 
replace date="01-jan-16" if date=="31-dec-15" 




gen annee=substr(date,8,2)
gen mois=substr(date,4,3)
gen jour=substr(date,1,2)

replace annee="1999" if annee=="99"
replace annee="2000" if annee=="00"
replace annee="2001" if annee=="01"
replace annee="2002" if annee=="02"
replace annee="2003" if annee=="03"
replace annee="2004" if annee=="04"
replace annee="2005" if annee=="05"
replace annee="2006" if annee=="06"
replace annee="2007" if annee=="07"
replace annee="2008" if annee=="08"
replace annee="2009" if annee=="09"
replace annee="2010" if annee=="10"
replace annee="2011" if annee=="11"
replace annee="2012" if annee=="12"
replace annee="2013" if annee=="13"
replace annee="2014" if annee=="14"
replace annee="2015" if annee=="15"
replace annee="2016" if annee=="16"
replace annee="2017" if annee=="17"
replace annee="2018" if annee=="18"


replace mois="01" if mois=="jan"
replace mois="02" if mois=="feb"
replace mois="03" if mois=="mar"
replace mois="04" if mois=="avr"
replace mois="05" if mois=="mai"
replace mois="06" if mois=="jun"
replace mois="07" if mois=="jul"
replace mois="08" if mois=="aug"
replace mois="09" if mois=="sep"
replace mois="10" if mois=="oct"
replace mois="11" if mois=="nov"
replace mois="12" if mois=="dec"


destring annee, replace force
destring mois, replace force
destring jour, replace force

gen date2 = mdy(mois, jour, annee)
gen week=wofd(date2)
format week %tw

keep if annee>=2007 & annee<=2018
drop date
drop annee mois date2


// Collapse weekly
collapse sp95_ht sp95_ttc sp98_ht sp98_ttc go_ht go_ttc, by(week)

save "$path\Intermediary Data\Weekly_data_NIPE.dta", replace




******** Brent data: extracted from Bloomberg ****

**** Daily ****
clear
import delimited "$path\Raw Data\Brent_Bloomberg.csv", delim(";") varnames(1)


gen annee=substr(date,length(date)-3,4)
replace date=substr(date,1,length(date)-4)
gen jour=substr(date,length(date)-2,3)
replace jour=subinstr(jour,"/","",.)
replace date=substr(date,1,length(date)-3)
replace date=subinstr(date,"/","",.)
rename date mois


destring annee, replace force
destring mois, replace force
destring jour, replace force

gen date2 = mdy(mois, jour, annee)
format date2 %d
keep if annee>=2007 & annee<=2018
drop annee mois jour
rename date2 date_tokeep

save "$path\Intermediary Data\Brent_daily.dta", replace



*** Weekly ****

clear
import delimited "$path\Raw Data\Brent_Bloomberg.csv", delim(";") varnames(1)


gen annee=substr(date,length(date)-3,4)
replace date=substr(date,1,length(date)-4)
gen jour=substr(date,length(date)-2,3)
replace jour=subinstr(jour,"/","",.)
replace date=substr(date,1,length(date)-3)
replace date=subinstr(date,"/","",.)
rename date mois


destring annee, replace force
destring mois, replace force
destring jour, replace force

gen date2 = mdy(mois, jour, annee)
gen week=wofd(date2)
format week %tw

keep if annee>=2007 & annee<=2018
drop date2
drop annee mois



// Collapse weekly
collapse brent, by(week)


save "$path\Intermediary Data\Brent_weekly.dta", replace


******* Rotterdam data ****


**** Daily ****
clear
import delimited "$path\Raw Data\Rotterdam.csv", delim(";") varnames(1)


gen annee=substr(date,length(date)-3,4)
replace date=substr(date,1,length(date)-4)
gen jour=substr(date,length(date)-2,3)
replace jour=subinstr(jour,"/","",.)
replace date=substr(date,1,length(date)-3)
replace date=subinstr(date,"/","",.)
rename date mois


destring annee, replace force
destring mois, replace force
destring jour, replace force

gen date2 = mdy(mois, jour, annee)
format date2 %d
keep if annee>=2007 & annee<=2018
drop annee mois jour
rename date2 date_tokeep

save "$path\Intermediary Data\Rotterdam_daily.dta", replace


*** Weekly ***

clear
import delimited "$path\Raw Data\Rotterdam.csv", delim(";") varnames(1)



gen annee=substr(date,length(date)-3,4)
replace date=substr(date,1,length(date)-4)
gen jour=substr(date,length(date)-2,3)
replace jour=subinstr(jour,"/","",.)
replace date=substr(date,1,length(date)-3)
replace date=subinstr(date,"/","",.)
rename date mois


destring annee, replace force
destring mois, replace force
destring jour, replace force

gen date2 = mdy(mois, jour, annee)
gen week=wofd(date2)
format week %tw

keep if annee>=2007 & annee<=2018
drop date2
drop annee mois


// Collapse weekly
drop jour
collapse  diesel_rotterdam gasoline_rotterdam, by(week)


save "$path\Intermediary Data\Rotterdam_weekly.dta", replace




********* Change euro dollars D5 *****
*** Daily ****

clear
import delimited "$path\Raw Data\eurusd_herokuapp.csv", delim(";") varnames(1)


gen annee=substr(date,length(date)-3,4)
replace date=substr(date,1,length(date)-4)
gen jour=substr(date,length(date)-2,3)
replace jour=subinstr(jour,"/","",.)
replace date=substr(date,1,length(date)-3)
replace date=subinstr(date,"/","",.)
rename date mois


destring annee, replace force
destring mois, replace force
destring jour, replace force

gen date2 = mdy(mois, jour, annee)
format date2 %d
keep if annee>=2007 & annee<=2018
drop annee mois jour
rename date2 date_tokeep

save "$path\Intermediary Data\eurusd_herokuapp_daily.dta", replace

***** Weekly *****
clear
import delimited "$path\Raw Data\eurusd_herokuapp.csv", delim(";") varnames(1)


gen annee=substr(date,length(date)-3,4)
replace date=substr(date,1,length(date)-4)
gen jour=substr(date,length(date)-2,3)
replace jour=subinstr(jour,"/","",.)
replace date=substr(date,1,length(date)-3)
replace date=subinstr(date,"/","",.)
rename date mois


destring annee, replace force
destring mois, replace force
destring jour, replace force

gen date2 = mdy(mois, jour, annee)
gen week=wofd(date2)
format week %tw

keep if annee>=2007 & annee<=2018
drop date2
drop annee mois


// Collapse weekly
drop jour
collapse eurusd, by(week)

save "$path\Intermediary Data\eurusd_herokuapp_weekly.dta", replace


*** Transform for merge

use "$path\Intermediary Data\Rotterdam_daily.dta", clear
gen date_maj_num=date_tokeep


merge 1:1 date_tokeep using "$path\Intermediary Data\eurusd_herokuapp_daily.dta"
drop _merge


**** Transformation of variables from USD to EUR ***

gen diesel_rotterdam_euro=diesel_rotterdam/eurusd
gen gasoline_rotterdam_euro=gasoline_rotterdam/eurusd

save "$path\Intermediary Data\DataReg_RotterdamBrent", replace


***

***  Create dataset of moving average of Rotterdam prices


clear
use "$path\Intermediary Data\Rotterdam_daily.dta"

merge 1:1 date_tokeep using "$path\Intermediary Data\eurusd_herokuapp_daily.dta"
drop _merge

tsset date_tokeep

replace diesel_rotterdam=diesel_rotterdam[_n-1] if diesel_rotterdam==.
replace gasoline_rotterdam=gasoline_rotterdam[_n-1] if gasoline_rotterdam==.
replace eurusd=eurusd[_n-1] if eurusd==.

gen diesel_rotterdam_euro=diesel_rotterdam/eurusd
gen gasoline_rotterdam_euro=gasoline_rotterdam/eurusd


gen ln_diesel_rotterdam0=log(diesel_rotterdam_euro)
gen ln_gasoline_rotterdam0=log(gasoline_rotterdam_euro)


tssmooth ma movav_ln_diesel_rotterdam=ln_diesel_rotterdam0, window(21 1 0)
tssmooth ma movav_ln_gasoline_rotterdam=ln_gasoline_rotterdam0, window(21 1 0)

gen resid_movav_diesel=ln_diesel_rotterdam0-movav_ln_diesel_rotterdam
gen resid_movav_gasoline=ln_gasoline_rotterdam0-movav_ln_gasoline_rotterdam

keep date_tokeep resid*
save "$path\Intermediary Data\DataReg_ResidMA_Rotterdam_daily.dta", replace

